.netCHARTING v10.3 Documentation
Data Engine

Data Engine

The DataEngine object connects to databases or consumes data objects such as a 'DataTable' and converts them to a SeriesCollection which can then be manipulated and used to generate a chart. The DataEngine offers many data manipulation features saving countless hours of development, particularly for date specific aggregation.

We encourage you to review the following tutorials before working with the DataEngine:

The basic concept of using the data engine is to specify a database, connection string and retrieve the SeriesCollection it generates. A basic example:

[C#]

DataEngine de = new DataEngine(connectionString,queryString);
SeriesCollection mySC = de.GetSeries();

[Visual Basic]

Dim de As New DataEngine(connectionString,queryString)
SeriesCollection mySC = de.GetSeries()

Filtering out date ranges

When specifying a start and end date in a database query, the following method should be used.

[C#]
DataEngine de = new DataEngine();     // Instantiate the data engine object
de.StartDate = new DateTime(2002,1,1,8,0,0);     // Specify the start date
de.EndDate = new DateTime(2002,1,1,23,59,59);      // Specify the end date.
//Specify a query.
de.SqlStatement = "SELECT names, values FROM myTable WHERE start > #StartDate# AND end < #EndDate#";

[Visual Basic]
Dim de As New DataEngine()     ' Instantiate the data engine object
de.StartDate = New DateTime(2002,1,1,8,0,0)     ' Specify the start date
de.EndDate = New DateTime(2002,1,1,23,59,59)      ' Specify the end date.
'Specify a query.
de.SqlStatement = "SELECT names, values FROM myTable WHERE start > #StartDate# AND end < #EndDate#"

Notes:

  • The #StartDate# and #EndDate# tokens can be used in SQL statements in which case the dates set for de.StartDate and de.EndDate will replace the tokens before the SqlStatement is executed.
  • StartDate and EndDate properties affects the dates listed in the title when Chart.ShowDateInTitle is used.
  • All date values in your SQL statements should be wrapped with the pound symbol: #10/25/2002#, regardless if Access or SQL server is used.
  • See also DataEngine.SqlStatement | DataEngine.StartDate | DataEngine.EndDate

Date grouping

This feature controls how the values of a given series are grouped by date. In order to use this option the first column returned by the SqlStatement must be a date/time data type.

Example 1

 

[C#]
de.SqlStatement = "SELECT time, unitsSold FROM sales";
de.DateGrouping = TimeInterval.Days;

[Visual Basic]
de.SqlStatement = "SELECT time, unitsSold FROM sales"
de.DateGrouping = TimeInterval.Days

The above will create an aggregated element for each day within the start and end date of your data. Similar options include:

  • Minutes
  • Hours
  • Days
  • Weeks
  • Months
  • Quarters
  • Years

Example 2

[C#]
myDataEngine.SqlStatement = "SELECT time, unitsSold FROM sales";
myDataEngine.DateGrouping = TimeInterval.Day;

[Visual Basic]
myDataEngine.SqlStatement = "SELECT time, unitsSold FROM sales"
myDataEngine.DateGrouping = TimeInterval.Day

When using day instead of days, data is grouped into 24 elements representing each hour of the day. For example, if the date range spans a week the element representing 11pm will contain the sum of all values that fall into that hour throughout the week.

Options include:

  • Hour ( 60 Minutes )
  • Day (24 Hours)
  • Week (7 Days)
  • Month (31 Days)
  • Quarter (3 Months)
  • Year ( 12 Months)

See also DataEngine.DateGrouping | TimeInterval

Limiting Data

Generated data can be limited in two ways. First, you can limit the number of elements returned for each series by using the 'Limit' property of the data engine.

[C#]
myDataEngine.Limit = "5";

[Visual Basic]
myDataEngine.Limit = "5"

Notes

  • This property is a string not a numeric value.
  • If DateGrouping is used, limit has no effect, you can limit the return with the StartDate and EndDate in such a case.
  • When data is limited, elements with the lowest y value are eliminated first.

The second way is to limit the number of series generated when using split by. For an example of SplitBy, see: Tutorials > Simple Queries > Multiple Series from a Single Query.

[C#]
myDataEngine.SplitByLimit = "2";

[Visual Basic]
myDataEngine.SplitByLimit = "2"

Notes

  • This property is a string not a numeric value.
  • SplitBy Defined - SplitBy occurs automatically based on the values returned by the SqlStatement property. When a 3rd column is returned from your SQL statement it automatically creates any number of new series based on the value provided. This value must be returned as a field in the SQL statement defined for the series. For example if you have a number of sales by customer, you could choose to graph those values by week in which case you would have 1 series with 4 values for a month or, using SplitBy, you could choose to split by customer and see the individual breakdown of sales by customer - with a separate series for each customer! See the SqlStatement property for more information.
  • Series with elements whose summed y values are the lowest are eliminated first.
  • See also DataEngine.Limit | DataEngine.SplitByLimit

Show data eliminated with Limit properties

The additional series not shown due to the use of SplitByLimit, or the additional elements not shown due to the use of Limit are aggregated into a single series or element respectively, and graphed alongside the main data when the ShowOther property is true.

[C#]
myDataEngine.ShowOther = true;
myDataEngine.OtherElementText = "The Rest";

[Visual Basic]
myDataEngine.ShowOther = True
myDataEngine.OtherElementText = "The Rest"

Notes:

Get data eliminated by Limit properties

If you would like to show series eliminated by Limit or SplitByLimit when drilling down or in a legend box, the LimitMode enumeration can be used. For example if you limit data to 5 and would like to see the rest, the LimitMode.ExcludeTop enumeration member can be used.

[C#]
myDataEngine.LimitMode = LimitMode.ExcludeTop;
myDataEngine.Limit = "5";

[Visual Basic]
myDataEngine.LimitMode = LimitMode.ExcludeTop
myDataEngine.Limit = "5"

Focus Limit on a specific Series

This feature allows limit to be bound to a specific series. First the series will be limited based on the specified value, then .netCHARTING will automatically match any remaining series to that limit order rather than limiting for each series independently.

[C#]

myDataEngine.Limit = "5";
myDataEngine.LimitPrimarySeries = "customers";

[Visual Basic]

myDataEngine.Limit = "5"
myDataEngine.LimitPrimarySeries = "customers"

Formatting

The data engine may populate an element's name property. The name property is a string, therefore, in order to ensure proper formatting of those values we can set the 'FormatString' and 'CultureName' properties of the data engine:

[C#]
myDataEngine.FormatString = "d";
myDataEngine.CultureName = "en-US";

[Visual Basic]
myDataEngine.FormatString = "d"
myDataEngine.CultureName = "en-US"

Notes: